﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

using System.Data;
using MySql.Data.MySqlClient;
using Dapper;

namespace WebApplication_01.class_helper
{
    public class DapperHelper
    {

        private int ID { get; set; }
        private String name { get; set; }
        private String test { get; set; }
        private String creatdate { get; set; }

        //Web.config配置链接：<add name="testDB" connectionString="server=175.24.81.65; database=testDB;uid=test;pwd=test;charset=utf8;port=3306" />

        /// <summary>
        /// 连接MySQL数据库
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection MySqlCon()
        {
            string mysqlConnectionStr = "server=175.24.81.65; database=test_001;uid=root;pwd=1qaz@wsX;charset=utf8;port=3306";
            var connection = new MySqlConnection(mysqlConnectionStr);
            connection.Open(); 
            return connection;
        }

        /// <summary>
                /// 执行insert与update脚本
                /// </summary>
        public int ExcuteSqlString(string sqlStr)
        {
            using (IDbConnection conn = DapperHelper.MySqlCon())
            {
                return conn.Execute(sqlStr);
            }
        }

        /// <summary>
                /// 执行查询脚本
                /// </summary>
        public List<T> QuerySqlString<T>(string sqlStr)
        {
            using (IDbConnection conn = DapperHelper.MySqlCon())
            {
                DynamicParameters para = new DynamicParameters();
                para.Add("", "");
                return conn.Query<T>(sqlStr) as List<T>;
            }
        }

        ////调用测试
        //public void Test()
        //{
        //    DapperHelper dh = new DapperHelper();

        //    //查询测试
        //    List<object> list = dh.QuerySqlString<object>("select * from infotest_01 limit 10;");

        //    ////执行测试
        //    //int result = dh.ExcuteSqlString("update TestTable set name='test' where id=1");
        //    //return;
        //}

        //select concat('private  ',(case `COLUMNS`.`DATA_TYPE` when 'varchar' then 'String' when 'char' then 'String' when 'text' then 'String' when 'int' then 'int' when 'tinyint' then 'int' when 'decimal' then 'double' when 'double' then 'double' when 'float' then 'double' else 'String' end),' ',`COLUMNS`.`COLUMN_NAME`,'{get;set;}',`COLUMNS`.`COLUMN_COMMENT`) AS `resultString`,`COLUMNS`.`COLUMN_NAME` AS `column_name`,`COLUMNS`.`DATA_TYPE` AS `data_type`,`COLUMNS`.`COLUMN_COMMENT` AS `column_comment` from `information_schema`.`COLUMNS` where (`COLUMNS`.`TABLE_NAME` = 'infotest_01')
    }
}


